package dao;

import entity.User;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class UserDAO {
    // 添加新用户
    public static int insert(User user) {
//        User user = new User("123456","Gn002","123456","顾青山02","ins ert测试成功！","男","1","33","蛤蟆仙人","木叶村 上忍 lsp");// 测试用句
        String url = "jdbc:mysql://82.156.38.140:3306/team_management_system?autoReconnect=TRUE&serverTimezone=UTC";
        String sqlUser = "root";
        String sqlPassword = "123456";
        Connection connection = null;
        PreparedStatement pstm = null;
        String sql = "INSERT INTO Group_User (id,account,password,nickname,profile,gender,level ,age,identity ,tag) VALUES(?,?,?,?,?,?,?,?,?,?)";
        int i = 0;
        try {
            DriverManager.registerDriver(new com.mysql.cj.jdbc.Driver());
            connection = DriverManager.getConnection(url, sqlUser, sqlPassword);
            pstm = connection.prepareStatement(sql);
            pstm.setString(1, String.valueOf(user.getId()));
            pstm.setString(2, user.getAccount());
            pstm.setString(3, user.getPassword());
            pstm.setString(4, user.getNickname());
            pstm.setString(5, user.getProfile());
            pstm.setString(6, user.getGender());
            pstm.setString(7, user.getLevel());
            pstm.setString(8, String.valueOf(user.getAge()));
            pstm.setString(9, user.getIdentity());
            pstm.setString(10, user.getTag());
            i = pstm.executeUpdate();
            System.out.println("成功插入" + i + "条数据");
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (pstm != null) {// pstm不为空时才关闭
                    pstm.close();
                }
                if (connection != null) {// 同上
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return i;
    }

    // 查询所有用户信息
    public static List<User> select() {
        String url = "jdbc:mysql://82.156.38.140:3306/team_management_system?autoReconnect=TRUE&serverTimezone=UTC";
        String sqlUser = "root";
        String sqlPassword = "123456";
        Connection connection = null;
        PreparedStatement pstm = null;
        String sql = "select * from Group_User";
        List<User> userList = new ArrayList<>();
        try {
            DriverManager.registerDriver(new com.mysql.cj.jdbc.Driver());
            connection = DriverManager.getConnection(url, sqlUser, sqlPassword);
            pstm = connection.prepareStatement(sql);
            ResultSet rs = pstm.executeQuery();
            // 遍历ResultSet,转为user对象存进userList
            while (rs.next() == true) {
                User user = new User(rs.getInt("id"), rs.getString("account"),
                        rs.getString("password"), rs.getString("nickname"),
                        rs.getString("profile"), rs.getString("gender"), rs.getString("level"),
                        rs.getInt("age"), rs.getString("identity"), rs.getString("tag"));
                userList.add(user);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (pstm != null) {// pstm不为空时才关闭
                    pstm.close();
                }
                if (connection != null) {// 同上
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return userList;
    }

    // 根据用户名，密码查询用户信息
    public static User selectOne(String account, String password) {
        String url = "jdbc:mysql://82.156.38.140:3306/team_management_system?autoReconnect=TRUE&serverTimezone=UTC";
        String sqlUser = "root";
        String sqlPassword = "123456";
        Connection connection = null;
        PreparedStatement pstm = null;
        String sql = "select * from Group_User where account='" + account + "' and password='" + password + "'";
        User userOne = null;
        try {
            DriverManager.registerDriver(new com.mysql.cj.jdbc.Driver());
            connection = DriverManager.getConnection(url, sqlUser, sqlPassword);
            pstm = connection.prepareStatement(sql);
            ResultSet rs = pstm.executeQuery();

            while (rs.next()) {
                if (rs.getString("age") == null) {
                    // 生成查询用户的对象
                    userOne = new User(rs.getInt("id"), rs.getString("account"),
                            rs.getString("password"), rs.getString("nickname"),
                            rs.getString("profile"), rs.getString("gender"), rs.getString("level"),
                            0, rs.getString("identity"), rs.getString("tag"));
                } else {
                    userOne = new User(rs.getInt("id"), rs.getString("account"),
                            rs.getString("password"), rs.getString("nickname"),
                            rs.getString("profile"), rs.getString("gender"), rs.getString("level"),
                            rs.getInt("age"), rs.getString("identity"), rs.getString("tag"));
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (pstm != null) {// pstm不为空时才关闭
                    pstm.close();
                }
                if (connection != null) {// 同上
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return userOne;
    }

    // 修改用户信息
    public static int update(User user) {
        String url = "jdbc:mysql://82.156.38.140:3306/team_management_system?autoReconnect=TRUE&serverTimezone=UTC";
        String sqlUser = "root";
        String sqlPassword = "123456";
        Connection connection = null;
        PreparedStatement pstm = null;
//        String sql = "UPDATE Group_User SET account='" + user.getAccount()
//                + "',password='" + user.getPassword() + "',nickname='" + user.getNickname() + "',profile='" + user.getProfile()
//                + "',gender='" + user.getGender() + "',level='" + user.getLevel() + "',age='" + user.getAge() + "',identity='" + user.getIdentity()
//                + "',tag='" + user.getTag() + "' WHERE account='" + user.getAccount() + "'";
        String sql = "UPDATE Group_User SET account=?, password=?,nickname=?,profile=?,gender=?,level=?,age=?,identity=?,tag=?" +
                " WHERE account=?";
        int i = 0;
        try {
            DriverManager.registerDriver(new com.mysql.cj.jdbc.Driver());
            connection = DriverManager.getConnection(url, sqlUser, sqlPassword);
            pstm = connection.prepareStatement(sql);
            pstm.setString(1,user.getAccount());
            pstm.setString(2,user.getPassword());
            pstm.setString(3,user.getNickname());
            pstm.setString(4,user.getProfile());
            pstm.setString(5,user.getGender());
            pstm.setString(6,user.getLevel());
            pstm.setInt(7,user.getAge());
            pstm.setString(8,user.getIdentity());
            pstm.setString(9,user.getTag());
            pstm.setString(10,user.getAccount());
            i = pstm.executeUpdate();
            System.out.println("成功修改了" + i + "条数据！");
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (pstm != null) {// pstm不为空时才关闭
                    pstm.close();
                }
                if (connection != null) {// 同上
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return i;
    }

    /**
     * queryModInUser
     *
     * @param type    char 搜索对象
     * @param strings String[] 搜索关键词
     * @return boolean
     */
    public boolean query(char type, String... strings) {
        //进入查询
        boolean res = false;//返回值
        Connection connection = null;//连接
        PreparedStatement ps = null;//操作对象
        ResultSet resultSet = null;//结果集
        try {
            //1.获取驱动
            DriverManager.registerDriver(new com.mysql.cj.jdbc.Driver());
            //2.获取连接
            connection = DriverManager.getConnection("jdbc:mysql://82.156.38.140/team_management_system", "root", "123456");
            //3.获取数据库操作对象
            String sql = "";
            switch (type) {
                case 'I':
                    sql = "SELECT * FROM Group_User WHERE Id=?";
                    ps = connection.prepareStatement(sql);
                    ps.setString(1, strings[0]);
                    break;
                case 'N':
                    sql = "SELECT * FROM Group_User WHERE nickname LIKE ?";
                    ps = connection.prepareStatement(sql);
                    ps.setString(1, "%" + strings[0] + "%");
                    break;
                case 'A':
                    sql = "SELECT * FROM Group_User WHERE account = ? AND password = ?";
                    ps = connection.prepareStatement(sql);
                    ps.setString(1, strings[0]);
                    ps.setString(2, strings[1]);
                    break;
            }
            //4.执行SQL语句
            resultSet = ps.executeQuery();
            //5.处理查询结果集
            if (resultSet.next()) res = true;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //从小到大分别释放资源
            try {
                if (resultSet != null) {
                    resultSet.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (ps != null) {
                    ps.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return res;
    }

}
